/*
 * Created on 2005-8-28
 *
 * TODO To change the template for this generated file go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
package com.store;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.jedi.BaseObj;
import com.tss.util.DbConn;
import com.tss.util.DbRs;

/**
 * @author dai
 *
 * TODO To change the template for this generated type comment go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
public class ApproveExpMgr extends BaseObj {
	public ApproveExpMgr() {}

	public DbRs getStat(String otherSQL) {
		return getStat(otherSQL,"");
	}
	
	public DbRs getStat(String otherSQL,String user_id) {
		clearErr();
		DbConn conn = new DbConn();
		DbRs rs = new DbRs();
		try {
			String sql = "";
			
			sql = "select a.user_name,a.role_id,a.user_id,CAST(b.approve_val as bigint)," +
					" c.appeal_cust,c.appeal_sales " +
					" from userinfo a left outer join (" +
					" select avg(approve_val) as approve_val,user_id from " +
					" t_approve_exp where approve_val > 0 ";
			if (otherSQL != null && !otherSQL.trim().equals(""))
				sql += otherSQL;
			sql += " group by user_id) as b " +
					" on a.user_id = b.user_id left outer join ( " +
					" select sum(appeal_cust) as appeal_cust," +
					" sum(appeal_sales) as appeal_sales,user_id " +
					" from t_approve_exp where 1=1 ";
			if (otherSQL != null && !otherSQL.trim().equals(""))
				sql += otherSQL;
			sql += " group by user_id) c on a.user_id = c.user_id " +
					" where (role_id = 'engineer' ) ";
			if (user_id != null && !user_id.trim().equals(""))
				sql += " and a.user_id = '" + user_id + "'";
			sql += " order by user_name";
			
			/*sql = "select a.*,b.user_name from (" +
				" select user_id,sum(approve_val) as approve_val,sum(appeal_cust) as appeal_cust,sum(appeal_sales) as appeal_sales " +
				" from t_approve_exp where 1=1";
			if (otherSQL != null && !otherSQL.trim().equals(""))
				sql += otherSQL;
			sql += " group by user_id) as a" +
				" inner join t_user_info b on a.user_id = b.user_id";*/
			conn.prepare(sql);
			rs = conn.executeQuery();
		}
		catch(Exception ex) {
			ex.printStackTrace();
			setErr(ex.getMessage());
		}
		finally {
			conn.close();
		}
		return rs;
	}
	
	public ApproveExp get(String id) {
		clearErr();
		ApproveExp approveExp = new ApproveExp(id);
		DbConn conn = new DbConn();
		try {
			String sql = "";
			sql = "select te.*,t.cusname  from t_approve_exp te" +
			" Left join task t on t.taskid=te.project_id " +
			" where te.list_id = ?";
			conn.prepare(sql);
			conn.setString(1,id);
			DbRs rs = conn.executeQuery();
			if (rs == null || rs.size() ==0) return null;
			approveExp.setUserId(get(rs,0,"user_id"));
			approveExp.setUserName(get(rs,0,"user_name"));
			approveExp.setApproveVal(getInt(rs,0,"approve_val"));
			approveExp.setAuthorId(get(rs,0,"author_id"));
			approveExp.setAuthorName(get(rs,0,"author_name"));
			approveExp.setProjectId(get(rs,0,"project_id"));
			approveExp.setAppealCust(getInt(rs,0,"appeal_cust"));
			approveExp.setAppealSales(getInt(rs,0,"appeal_sales"));
			approveExp.setExpDesc(get(rs,0,"exp_desc"));
			approveExp.setExpTime(get(rs,0,"exp_time"));
			approveExp.setProject_name(get(rs,0,"cusname"));
			return approveExp;
		}
		catch(Exception ex) {
			ex.printStackTrace();
			setErr(ex.getMessage());
			return null;
		}
		finally {
			conn.close();
		}
	}
	
	public List get(String otherSQL,String orderField) {
		clearErr();
		DbConn conn = new DbConn();
		DbRs rs = null;
		try {
			String sql = "";
			sql = "select * from t_approve_exp where 1=1";
			if (otherSQL != null && !otherSQL.trim().equals("")) 
				sql += otherSQL;
			if (orderField != null && !orderField.trim().equals(""))
				sql += " order by " + orderField;
			if (getTopNum() > 0)
				sql += " limit " + getTopNum();
			conn.prepare(sql);
			rs = conn.executeQuery();
		}
		catch(Exception ex) {
			ex.printStackTrace();
			setErr(ex.getMessage());
			return null;
		}
		finally {
			conn.close();
		}
		if (rs != null && rs.size() >0) {
			List r = new ArrayList();
			for(int i=0;i<rs.size();i++) {
				ApproveExp approveExp = new ApproveExp(get(rs,i,"list_id"));
				approveExp.setUserId(get(rs,i,"user_id"));
				approveExp.setUserName(get(rs,i,"user_name"));
				approveExp.setApproveVal(getInt(rs,i,"approve_val"));
				approveExp.setAuthorId(get(rs,i,"author_id"));
				approveExp.setAuthorName(get(rs,i,"author_name"));
				approveExp.setProjectId(get(rs,i,"project_id"));
				approveExp.setAppealCust(getInt(rs,i,"appeal_cust"));
				approveExp.setAppealSales(getInt(rs,i,"appeal_sales"));
				approveExp.setExpDesc(get(rs,i,"exp_desc"));
				approveExp.setExpTime(get(rs,i,"exp_time"));
				r.add(approveExp);
			}
			return r;
		}
		return null;
	}
	
	public List get(String otherSQL,String orderField,int pageNo, int pageSize) {
		clearErr();
		DbRs rs = null;
		DbConn conn = null;
		try {
			conn = new DbConn();
			String sqlCount = "select count(*) as total from t_approve_exp where 1=1";
			if (otherSQL != null && !otherSQL.trim().equals(""))
				sqlCount += otherSQL;
			conn.prepare(sqlCount);
			DbRs rsCount = conn.executeQuery();
			int total = getInt(rsCount,0,"total");
			setTotal(total);
			boolean b = total % pageSize == 0;
			int pageCount = b ? (total / pageSize) : (total / pageSize + 1);
			if (pageNo > pageCount) pageNo = pageCount;
			String sql = "select te.*,t.cusname from t_approve_exp te " +
					" Left join task t on t.taskid=te.project_id " +
					" where 1=1";
			if (otherSQL != null && !otherSQL.trim().equals(""))
				sql += otherSQL;
			if (orderField != null && !orderField.trim().equals(""))
				sql += " order by " + orderField;
			conn.prepare(sql);
			rs = conn.executeQuery(pageNo, pageSize);
		} catch (SQLException ex) {
			ex.printStackTrace();
			setErr(ex.getMessage());
			return new ArrayList();
		} catch (Exception e) {
			e.printStackTrace();
			setErr(e.getMessage());
			return new ArrayList();
		} finally {
			conn.close();
		}
		List r = new ArrayList();
		if (rs == null || rs.size() <= 0)
			return r;
		for (int i = 0; i < rs.size(); i++) {
			ApproveExp approveExp = new ApproveExp(get(rs,i,"list_id"));
			approveExp.setUserId(get(rs,i,"user_id"));
			approveExp.setUserName(get(rs,i,"user_name"));
			approveExp.setApproveVal(getInt(rs,i,"approve_val"));
			approveExp.setAuthorId(get(rs,i,"author_id"));
			approveExp.setAuthorName(get(rs,i,"author_name"));
			approveExp.setProjectId(get(rs,i,"project_id"));
			approveExp.setAppealCust(getInt(rs,i,"appeal_cust"));
			approveExp.setAppealSales(getInt(rs,i,"appeal_sales"));
			approveExp.setExpDesc(get(rs,i,"exp_desc"));
			approveExp.setExpTime(get(rs,i,"exp_time"));
			approveExp.setProject_name(get(rs,i,"cusname"));
			r.add(approveExp);
		}
		return r;
	}
}
